// .........................................................................
// Title: insurance_panel.do
//
// Constructs compact summary versions of the insurance holdings data
// .........................................................................

* --------------------------------------
* Construct insurer asset size ranks
* --------------------------------------

* Insurer summary, quarterly
use "$insurance_holdings/all_insurance_master", clear
cap rename entity_name holder_name
drop if missing(cusip)
gcollapse (sum) marketvalue_usd, by(holder_name cusip date_q)
mmerge cusip date_q using "$tmp/holdings_mf_ins_trace_universe_refined", unmatched(m)
keep if _merge == 3
save "$tmp/insurer_summary_trace_q", replace

* IG sample
use "$tmp/insurer_summary_trace_q", clear
gen year = year(dofq(date_q))
mmerge cusip year using "$tmp/issue_ratings_merged_sp_moodys_y", unmatched(m)
drop _merge
keep if inlist(rating_cat, "A", "AA", "AAA", "BBB")
save "$tmp/insurer_summary_trace_q_ig", replace

* Insurer average size
use "$tmp/insurer_summary_trace_q_ig", clear
gcollapse (sum) marketvalue_usd, by(holder_name date_q)
gcollapse (mean) marketvalue_usd, by(holder_name)
rename marketvalue_usd insurer_avg_size
save "$tmp/insurer_avg_size", replace

* Insurer ranks
use "$tmp/insurer_avg_size", clear
gsort -insurer_avg_size
gen insurer_avg_rank = _n
save "$tmp/insurer_avg_rank", replace

* --------------------------------------
* Initial holdings panel
* --------------------------------------

* complete ranking
use "$insurance_holdings/all_insurance_master", clear
cap rename entity_name holder_name
gcollapse (sum) marketvalue_usd, by(holder_name date_q)
gcollapse (mean) marketvalue_usd, by(holder_name)
gsort -marketvalue_usd
gen insurer_avg_rank = _n
keep holder_name insurer_avg_rank marketvalue_usd
rename marketvalue_usd insurer_avg_size
save "$tmp/insurer_avg_rank_full", replace

* construct panel from full holdings
use "$insurance_holdings/all_insurance_master", clear
rename entity_name holder_name
mmerge holder_name using "$tmp/insurer_avg_rank_full", unmatched(m)
drop _merge
encode holder_name, gen(_holder)
drop if missing(cusip)
egen holder_security = group(_holder cusip)
collapse (sum) carrying_value conditional_fair_value actual_cost shares_owned aggregate_par_value marketvalue_usd ///
    (firstnm) issuer_name asset_issuer_type asset_description maturity_date ///
    as_reported_issuer_type asset_class insurer_type insurer_avg_size insurer_avg_rank, ///
    by(holder_security _holder holder_name cusip date_q)
xtset holder_security date_q
save "$tmp/insurance_holdings_q_panel", replace

* Holdings at issuance
use "$tmp/insurer_summary_trace_q_ig", clear
gen issuance_quarter = qofd(issuance_date)
drop if missing(issuance_date)
drop if missing(issuance_quarter)
format %tq issuance_quarter
keep if date_q == issuance_quarter
save "$tmp/holdings_at_issuance_by_insurer_ig", replace

* SPMI insurer keys to insurer name crosswalk
import excel using "$raw/sp_insurance/additional/sp_insurer_keys.xls", clear
rename A holder_name
rename B holder_key
save "$tmp/sp_holder_keys", replace

* --------------------------------------
* Extended corporate bond panel
* --------------------------------------

* Corporate bond panel
use "$tmp/insurance_holdings_q_panel", clear
mmerge cusip using "$raw/cmns/gcap_security_master_cusip", unmatched(m)
keep if class_code2 == "BC"
drop _merge asset_class*
drop fsym_id class_code* figi isin as_reported_issuer_type asset_issuer_type
drop holder_security

* Add in the keys
mmerge holder_name using "$tmp/sp_holder_keys", unmatched(m)
drop _merge

* convert par to market
mmerge cusip date_q using "$tmp/trace_clean_prices_q", unmatched(m)
drop _merge
cap drop marked_par_value
gen marked_par_value = aggregate_par_value * trace_median_price_w / 100
order marked_par_value
replace marketvalue_usd = marketvalue_usd / 1e3
gen clean_marketvalue = marked_par_value
replace clean_marketvalue = marketvalue_usd if missing(clean_marketvalue)
drop _holder
order holder_name holder_key cusip date_q *value*
replace insurer_avg_size = insurer_avg_size / 1e6

* merge in ratings
cap drop year
gen year = year(dofq(date_q))
mmerge cusip year using "$tmp/issue_ratings_merged_sp_moodys_y", unmatched(m)
drop _merge

* portfolio totals and shares
drop if missing(holder_name) | missing(cusip)
gsort holder_name date_q
by holder_name date_q: egen bc_portfolio = total(clean_marketvalue)
gen bc_portfolio_share = clean_marketvalue / bc_portfolio

* sorting
egen holder_security = group(holder_name cusip)

* add in amounts outstandings and shares
mmerge cusip year using "$tmp/amounts_outstanding_y_mv", unmatched(m)
gsort cusip date_q
by cusip date_q: egen tot_insurance_holdings = total(clean_marketvalue)
gen share_ins_issuance = (tot_insurance_holdings / 1e3) / value_outstanding_mv
gen share_holder_issuance = (clean_marketvalue / 1e3) / value_outstanding_mv
gen share_holder_ins = clean_marketvalue / tot_insurance_holdings
gsort holder_security date_q
xtset holder_security date_q
save "$tmp/insurance_corporate_bond_holdings", replace

* extended version with aggregation
gen issuer_number = substr(cusip, 1, 6)
mmerge issuer_number using "$raw/cmns/cmns_aggregation", unmatched(m)
drop _merge *source*
save "$tmp/insurance_corporate_bond_holdings_x", replace

* --------------------------------------
* Equity holdings summary
* --------------------------------------

* Equity panel
use "$tmp/insurance_holdings_q_panel", clear
mmerge cusip using "$raw/cmns/gcap_security_master_cusip", unmatched(m) ukeep(class_code1 class_code2)
keep if class_code1 == "E"
gcollapse (sum) marketvalue_usd, by(holder_name cusip date_q)
save "$tmp/insurer_equity_holdings", replace
